# sql1 = "insert into 表名 values();"
# sql2 = "delete from 表名 where 条件;"
# sql3 = "update 表名 set …… where 条件;"
# sql4 = "select 字段/* from 表名 where 条件;"
import xlrd
import pymysql
def read_from_excel(index):#index 是第几个表 从0开始计数
    # 打开excel（工作簿）
    wb = xlrd.open_workbook(r"C:\Users\dell\Documents\WeChat Files\wxid_6w331j0pnm4d22\FileStorage\File\2022-08\5. baidu-员工的人员信息.xls")  # 打开工作簿
    ws = wb.sheet_by_index(index)  # 打开表
    nrows = ws.nrows  # 获取行数
    # ncols = ws.ncols  # 获取列数
    data = []
    for i in range(1,nrows):#从第1行传到最后一行
        data.append(ws.row_values(i))
    return data
# 增
# def insert_into_mysql(sql,param):
#     # 连接数据库
#     connect = pymysql.connect(host="localhost",user="root",password="123456",database="百度员工的人员信息")
#     # 创建控制台
#     cursor = connect.cursor()
#     # 执行sql
#     cursor.executemany(sql,param)
#     # 提交
#     connect.commit()
#     print("提交成功！")
#     # 关闭控制台
#     cursor.close()
#     # 关闭连接
#     connect.close()
# sql1 = "insert into baidu values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
# data1 = read_from_excel(0)
# insert_into_mysql(sql1,data1)

# #查
def select_from_mysql(sql):
    # 连接数据库
    connect = pymysql.connect(host="localhost",user="root",password="123456",database="百度员工的人员信息")
    # 创建控制台
    cursor = connect.cursor()
    # 执行sql
    cursor.execute(sql)
    # 关闭控制台
    cursor.close()
    # 关闭连接
    connect.close()
    #return cursor.fetchall()#查询数量
    return cursor()
#总人数
# sql = "select count(*) from baidu;"
# print(select_from_mysql(sql)[0][0])
# #占比
# sql = "select count(*) from baidu where '电话号码' like '134%' or '135%'" \
#       " or '136%' or '137%' or '138%' or '139%' or '147%' or '150%' or" \
#       " '151%' or '152%' or '157%' or '158%' or '159%' or '178%' or '182%'" \
#       " or '183%' or '184%' or '187%' or '188%' or '1703%' or '1705%' or '1706%';"
# print(select_from_mysql(sql)[0][0])
# sql = "select count(*) from baidu where '手机号' like '133%' or '130%';"
# print(select_from_mysql(sql)[0][0])
#
sql = "select '男' from baidu where '性别';"
print(select_from_mysql(sql)[0][0])
# sql = "select count(*) from student where sname like '张%三';"
# print(select_from_mysql(sql)[0][0])